from DataAccess.DBConnFactory import *

from Misc.Utils import *
from string import Template

def query_equity_cap(ref_date, tick_list = None, exch_list = ['CNSESH', 'CNSESZ']):
	
	sql_tpl = Template('''select sc.SYMBOL, ec.eq_cpt
						  from (select COMPANYCODE,
									   SCSTC1 * 10000 as eq_cpt,
									   rank() over(partition by COMPANYCODE order by PublishDate desc) as rk
								  from SCSTC
								 where PublishDate <= TO_DATE('${DATE}', 'yyyy-mm-dd')) ec,
							   SECURITYCODE sc
							${TICK_CRITERIA}
							and ec.COMPANYCODE = sc.COMPANYCODE
							and ${EXCH_CRITERIA}
							and ec.rk = 1''')
	sql_text = sql_tpl.substitute(DATE = ref_date.isoformat(),
									EXCH_CRITERIA = format_sql_criteria('sc.EXCHANGE', exch_list),
									TICK_CRITERIA = format_sql_tick_criteria('sc.SYMBOL', tick_list))

	cursor = DBConnFactory().get_db_connection('FINCHINA').cursor()
	cursor.execute(sql_text)
	r = cursor.fetchall()
	
	equity_cap = {}	
	for elem in r:
		equity_cap[elem[0]] = elem[1]
			
	return equity_cap